SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMDOCC_GENE_Q01]        
/*------------------------------------------------------*/            
/*---Empresa               OFISIS S.A.                --*/            
/*---CLIEnte               COSMOS SAC                 --*/            
/*---Sistema               Tesoreria                  --*/            
/*---M½dulo                Empresa                    --*/            
/*---Programa              ESTADO DE CUENTA           --*/            
/*---Script                SP_TMDOCC_GENE_Q01.sql     --*/            
/*---Nombre SP             SP_TMDOCC_GENE_Q01         --*/            
/*---Desarrollado por      Carlos R. Marquez Escobar  --*/            
/*---Fecha Creaci½n        03/04/2009                 --*/            
/*---Base Datos            SQL        --*/            
/*---Versi½n               8.0                        --*/            
/*---Invoca a SP                                      --*/            
/*------------------------------------------------------*/            
/*---- MODIF POR : PERCY GARCIA  ---*/      
/*---- FECHA MODIF : 18/05/2009 ---*/      
/*--- DETALLE MODIF : SE AUMENTO AGRUP POR PAGADOR --*/      
/*------------------------------------------------------*/            
/*--- Drop Proc SP_TMDOCC_GENE_Q01                     --*/            
/*---Grant execute on SP_TMDOCC_GENE_Q01 to public         --*/             
/*---       
Execute SP_TMDOCC_GENE_Q01_percy " Where TMDOCC_GENE.CO_EMPR in ( '01') And TMDOCC_GENE.CO_TIPO_DOCU Not in ( 'ANT','DEP','NTC') ",       
'', 'DOL', 'N', 3.02,       
'C', 3,'P','D', '05/18/2009'      
      
      
Execute SP_TMDOCC_GENE_Q01_percy "   Where TMDOCC_GENE.CO_EMPR in ( '01') And TMDOCC_GENE.CO_TIPO_DOCU Not in ( 'ANT','DEP','NTC') And TMDOCC_GENE.CO_PAGA Like '%20510029462%' ",       
'', 'DOL', 'N', 3.011,       
'C', 3,'P','D', '05/19/2009'      
      
--*/       
/*------------------------------------------------------*/            
@ISCA_WHER_0001  VARCHAR(255),      
@ISCA_WHER_0002  VARCHAR(255),            
@ISCO_MONE_PRES  TD_VC_003,      
@ISST_USUA   TD_VC_001,            
@INFA_USUA   TD_NU_009_004 ,      
@ISTI_AUXI_CLIE  TD_VC_001,      
@ISTI_REPO   TD_VC_001,      
@ISMO_REPO   TD_VC_001,      
@ISFO_REPO   TD_VC_001,      
@IDFE_REPO   TD_DT_001 --- Campo Fecha de la pantalla             
AS      
CREATE TABLE #TWDOCU_CLIE_Q32_1             
 ( CO_EMPR varchar(3) NULL,            
   TI_CLIE varchar(3) NULL ,            
   CO_CLIE varchar(20) NULL ,            
   CO_TIPO_DOCU varchar(3) NULL ,            
   NU_DOCU_CLIE varchar(20) NULL,            
   FE_REPO datetime NULL,            
   FE_EMIS datetime NULL,            
   FE_VENC datetime NULL,        
   FE_DESP datetime NULL,            
   CO_TIPO_OPER varchar(3) NULL,            
   CO_MONE varchar(03) NULL,            
   FA_TIPO_CAMB numeric(9,4) NULL,            
   IM_CXCB numeric(16,4) DEFAULT 0 null,            
   IM_PAGO numeric(16,4) DEFAULT 0 null,            
   IM_CXCB_CONV numeric(16,4) DEFAULT 0 null,            
   IM_PAGO_CONV numeric(16,4) DEFAULT 0 null,            
   IM_CONV numeric(16,4) DEFAULT 0 null,            
   IM_CONV_TOTA numeric(16,4) DEFAULT 0 null,            
   TI_DETA varchar(1) NULL,             
   ST_SIGN varchar(1) NULL,      
CO_PAGA Varchar(20) NULL      
)            
            
CREATE TABLE #TWDOCU_CLIE_Q32_2           
 ( CO_EMPR varchar(3) NULL,            
   TI_CLIE varchar(3) NULL ,            
   CO_CLIE varchar(20) NULL ,            
   CO_TIPO_DOCU varchar(3) NULL ,            
   NU_DOCU_CLIE varchar(20) NULL,            
   FE_REPO datetime NULL,            
   FE_EMIS datetime NULL,            
   FE_VENC datetime NULL,        
   FE_DESP datetime NULL,            
   CO_TIPO_OPER varchar(3) NULL,            
   CO_MONE varchar(03) NULL,            
   FA_TIPO_CAMB numeric(9,4) NULL,            
   IM_CXCB numeric(16,4) DEFAULT 0 null,            
   IM_PAGO numeric(16,4) DEFAULT 0 null,            
   IM_CXCB_CONV numeric(16,4) DEFAULT 0 null,            
   IM_PAGO_CONV numeric(16,4) DEFAULT 0 null,      
      
 NU_ANTI  INTEGER null,      
 IM_XVEN  NUMERIC(16,4) DEFAULT 0 null,      
 DIAS_0030 NUMERIC(16,4) DEFAULT 0 null,      
 DIAS_0060 NUMERIC(16,4) DEFAULT 0 null,      
 DIAS_0090 NUMERIC(16,4) DEFAULT 0 null,      
 DIAS_0180 NUMERIC(16,4) DEFAULT 0 null,      
 DIAS_0360 NUMERIC(16,4) DEFAULT 0 null,      
 DIAS_AMAS NUMERIC(16,4) DEFAULT 0 null,      
CO_PAGA Varchar(20) NULL       
 )      
      
CREATE TABLE #TWDOCU_CLIE_Q32_3          
 ( CO_EMPR  varchar(3) NULL,      
 TI_CLIE  varchar(3) NULL,      
 CO_CLIE  varchar(20) NULL,      
 CO_GRUP_ECON varchar(6) NULL,      
 CO_MONE  varchar(03) NULL,      
 CO_TIPO_DOCU varchar(3) NULL ,              
 TO_DIAS_0030 NUMERIC(16,4) DEFAULT 0 null ,      
 TO_DIAS_0060 NUMERIC(16,4) DEFAULT 0 null,      
 TO_DIAS_0090 NUMERIC(16,4) DEFAULT 0 null,      
 TO_DIAS_0180 NUMERIC(16,4) DEFAULT 0 null,      
 TO_DIAS_0360 NUMERIC(16,4) DEFAULT 0 null,      
 TO_DIAS_AMAS NUMERIC(16,4) DEFAULT 0 null,      
 TO_DEUD_VENC NUMERIC(16,4) DEFAULT 0 null,      
 PC_DEUD_VENC NUMERIC(09,4) DEFAULT 0 null,      
 TO_XVEN   NUMERIC(16,4) DEFAULT 0 null,      
 TO_DEUD_CONS NUMERIC(16,4) DEFAULT 0 null      
 )      
      
      
CREATE TABLE #TWLIE_Q32_0      
 ( CO_EMPR  varchar( 3) NULL,            
 CO_CLIE  varchar(20) NULL,      
 CO_GRUP_ECON varchar( 6) NULL,      
 TO_DEUD_VENC NUMERIC(16,4) DEFAULT 0,      
 ST_GRUP  varchar( 1) NULL,       
CO_PAGA Varchar(20) NULL      
 )      
      
       
CREATE TABLE  #TWCLIE_RESU        
 ( TI_INGR  VARCHAR(20) NULL,        
 AL_TOTA  NUMERIC(16,4) NULL DEFAULT 0 ,        
 AL_VIGE  NUMERIC(16,4) NULL DEFAULT 0  ,        
 AL_VENC  NUMERIC(16,4) NULL DEFAULT 0 ,        
 DE_01_30 NUMERIC(16,4) NULL DEFAULT 0 ,        
 DE_31_60 NUMERIC(16,4) NULL DEFAULT 0 ,        
 DE_61_90 NUMERIC(16,4) NULL DEFAULT 0 ,        
 DE_91_180 NUMERIC(16,4) NULL DEFAULT 0 ,        
 DE_181_360 NUMERIC(16,4) NULL DEFAULT 0 ,        
 DE_361_MAS NUMERIC(16,4) NULL DEFAULT 0          
 )        
      
CREATE INDEX ITWDOCU_CLIE_Q32_1_01 ON #TWDOCU_CLIE_Q32_1 (CO_EMPR) ON [PRIMARY]        
CREATE INDEX ITWDOCU_CLIE_Q32_2_01 ON #TWDOCU_CLIE_Q32_2 (NU_ANTI) ON [PRIMARY]        
CREATE INDEX ITWDOCU_CLIE_Q32_3_01 ON #TWDOCU_CLIE_Q32_3 (CO_EMPR,CO_CLIE) ON [PRIMARY]        
      
DECLARE             
@VSCA_WHER   VARCHAR(600),      
@VSST_ANTC   TD_VC_001,      
      
@OSTI_DOCU_LETR  TD_VC_003,      
@OSTI_DOCU_NCRE  TD_VC_003,      
@OSTI_DOCU_OCMP  TD_VC_003,      
@OSTI_DOCU_CHEQ  TD_VC_003,      
      
@VNFA_USUA   TD_NU_009_004,      
      
@VNTO_DEUD_VENC  NUMERIC(16,4),      
@VNTO_DEUD_CONS  NUMERIC(16,4)      
      
      
EXEC SP_TTDOCU_CNTB_Q01 4, @OSTI_DOCU_NCRE OUTPUT            
EXEC SP_TTDOCU_CNTB_Q01 6, @OSTI_DOCU_LETR OUTPUT            
/* Devuelve el Tipo Documento CHEQUE */              
Exec SP_TTDOCU_CNTB_Q01 29, @OSTI_DOCU_CHEQ Output            
            
SELECT @VSCA_WHER = REPLACE(@ISCA_WHER_0001 + @ISCA_WHER_0002, "TMDOCC_GENE", "T4")            
            
            
IF PATINDEX("%ANT%", @VSCA_WHER) > 0            
    SELECT @VSST_ANTC = "S"            
ELSE            
    SELECT @VSST_ANTC = "N"            
            
           
-- TIPOS DE DETALLE(TI_DETA = "1" AL "7")            
--1.- DOCS EMITIDOS EN ESOS MESES                 
--2.- PAGOS BANCOS / CAJA            
--3.- APLICACIONES DE NCR A DOCUMENTOS            
--4.- CANJES            
--5.- RENOVACIONES            
--6.- APLICACION DE DOC. A ANTICIPOS            
--8.- ANTICIPOS( SIN APLICACIONES, SALDO = IMPORTE DEPOSITO )            
--7.- ANTICIPOS( CON APLICACION, SALDOS)            
            
--1.- TODOS LOS DOCUMENTOS EMITIDOS EN ESOS MESES            
-- TMDOCC_GENE       
      
  EXECUTE( "INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, TI_CLIE, FE_REPO, FE_EMIS, FE_VENC,       
  FE_DESP, CO_TIPO_OPER, CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA,      
 ST_SIGN,CO_PAGA)            
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU , T4.NU_DOCU_CLIE , T4.CO_CLIE, T4.TI_CLIE,     
   T4.FE_EMIS, T4.FE_EMIS, T4.FE_VENC,       
   T4.FE_DESP, T4.CO_TIPO_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,        ---Verif Fech Despacho      
   ( PatIndex(T2.ST_SIGN,'S') - PatIndex(T2.ST_SIGN,'N') ) * T4.IM_TOTA, '1',      
   T2.ST_SIGN,t4.CO_CLIE      
  From  TMDOCC_GENE T4, TTDOCU_CNTB T2 "             
  + @VSCA_WHER + "            
  And T4.TI_DOCU_ANTC ='N'            
  And T4.CO_ESTA_DOCU != 'ANU'            
  AND T4.CO_TIPO_DOCU = T2.TI_DOCU")          
      
      
            
--2.- PAGOS BANCOS / CAJA            
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, FE_EMIS,              
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.CO_TIPO_OPER, T2.FE_OPER,            
  T4.FE_EMIS, T4.CO_MONE, T2.FA_TIPO_CAMB,            
  ISNULL(( PatIndex(T4.ST_SIGN,'S') - PatIndex(T4.ST_SIGN,'N') ) *            
  ( PatIndex(T1.ST_CIER_DOCU, 'N') * T1.IM_CONV_DOCU +            
    PatIndex(T1.ST_CIER_DOCU, 'S') * T1.IM_SALD_DOCU ),0), '2',t4.CO_PAGA      
  From  TDMOVI_BANC T1, TCMOVI_BANC T2, #TWDOCU_CLIE_Q32_1 T4      
  Where T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'             
   And T2.TI_SITU != 'ANU'             
   And T2.TI_SITU != 'EXT'                
      
   And T1.CO_EMPR = T2.CO_EMPR              
   And T1.CO_UNID_CONC = T2.CO_UNID_CONC        
   And T1.CO_BANC = T2.CO_BANC              
   And T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC              
   And T1.AA_BNCO = T2.AA_BNCO            
   And T1.MM_BNCO = T2.MM_BNCO             
   And T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC             
   And T1.NU_COMP_BANC = T2.NU_COMP_BANC            
            
   And T1.CO_EMPR = T4.CO_EMPR            
   And T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU            
   And T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE            
   And T4.TI_DETA = '1' ")            
          
             
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, FE_EMIS,         
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.CO_TIPO_OPER, T2.FE_OPER,            
  T4.FE_EMIS, T4.CO_MONE, T2.FA_TIPO_CAMB,            
  ISNULL(( PatIndex(T4.ST_SIGN,'S') - PatIndex(T4.ST_SIGN,'N') ) *            
  ( PatIndex(T1.ST_CIER_DOCU, 'N') * T1.IM_CONV_DOCU +            
    PatIndex(T1.ST_CIER_DOCU, 'S') * T1.IM_SALD_DOCU),0), '2',t4.CO_PAGA            
  From   TDMOVI_CAJA T1, TCMOVI_CAJA T2, #TWDOCU_CLIE_Q32_1 T4      
  Where T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'            
   And T2.TI_SITU != 'ANU'             
   And T2.TI_SITU != 'EXT'              
   And T1.CO_EMPR = T2.CO_EMPR              
   And T1.CO_CAJA = T2.CO_CAJA            
   And T1.AA_CAJA = T2.AA_CAJA            
   And T1.MM_CAJA = T2.MM_CAJA             
   And T1.TI_MOVI = T2.TI_MOVI            
   And T1.NU_COMP_CAJA = T2.NU_COMP_CAJA            
            
   And T1.CO_EMPR = T4.CO_EMPR            
   And T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU            
   And T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE      
   And T4.TI_DETA = '1'       
  ")      
            
      
-- PAGOS A CHEQUES DE LA TMDOCU_CLIE EN BANCOS, CON TDCHEQ_BANC            
 EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, FE_EMIS, FE_VENC,           
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  Select T1.CO_EMPR, T4.CO_TIPO_DOCU, T1.NU_CHEQ, T1.CO_ENTI, T4.FE_EMIS,             
  T4.FE_EMIS, T4.FE_VENC, T4.CO_MONE, T2.FA_TIPO_CAMB,            
  ISNULL(( PatIndex(T4.ST_SIGN,'S') - PatIndex(T4.ST_SIGN,'N') ) * T1.IM_CHEQ,0), '2',t4.CO_PAGA            
  From  TDCHEQ_BANC T1, TCMOVI_BANC T2, #TWDOCU_CLIE_Q32_1 T4      
  Where T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'            
   And T2.TI_SITU = 'ACT'                
   And T1.CO_EMPR = T2.CO_EMPR              
   And T1.CO_BANC = T2.CO_BANC              
   And T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC              
   And T1.AA_BNCO = T2.AA_BNCO            
   And T1.MM_BNCO = T2.MM_BNCO             
   And T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC             
   And T1.NU_COMP_BANC = T2.NU_COMP_BANC            
            
   And T1.CO_EMPR = T4.CO_EMPR            
   And T4.CO_TIPO_DOCU  = '" + @OSTI_DOCU_CHEQ + "'          
   And T1.NU_CHEQ = T4.NU_DOCU_CLIE            
   And T4.TI_DETA = '1'      
 ")            
       
            
--3.- APLICACIONES DE NOTA DE CREDITOS A DOCUMENTOS            
-- DOCUMENTOS            
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO,             
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T2.CO_ENTI, T1.CO_TIPO_OPER, T1.FE_AMAR,            
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB,             
 ISNULL((PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) *         
 (PatIndex(T2.ST_CIER_DOCU,'N') * ISNULL(T2.IM_COBR_CDES, 0) +           
  PatIndex(T2.ST_CIER_DOCU,'S') * T2.IM_SALD_CDES ),0), '3',t4.CO_PAGA          
  From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2, #TWDOCU_CLIE_Q32_1 T4, TTDOCU_CNTB T5      
  Where T1.CO_EMPR =   T4.CO_EMPR            
   And T1.TI_SITU = 'ACT'            
   And T2.CO_TIDO_DEST = T4.CO_TIPO_DOCU             
   And T2.NU_DOCU_DEST = T4.NU_DOCU_CLIE             
   And T1.CO_EMPR = T2.CO_EMPR            
   And T1.CO_UNID_CONC = T2.CO_UNID_CONC           
   And T1.NU_AMAR = T2.NU_AMAR              
   And T1.FE_AMAR = T2.FE_AMAR            
   And T2.CO_EMPR = T4.CO_EMPR            
   And T2.CO_ENTI = T4.CO_CLIE            
   And T2.CO_TIDO_DEST = T4.CO_TIPO_DOCU            
   And T2.NU_DOCU_DEST = T4.NU_DOCU_CLIE      
   And T4.TI_DETA = '1'            
      
   And T4.CO_TIPO_DOCU = T5.TI_DOCU            
   And T5.ST_DOCU_0004 = 'N' ")            
         
            
-- NOTAS DE CREDITO APLICADAS             
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO,             
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T2.CO_ENTI, T1.CO_TIPO_OPER, T1.FE_AMAR,            
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB,            
 ISNULL( (PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) *         
  (PatIndex(T2.ST_CIER_DOCU,'N') * ISNULL(T2.IM_COBR_CORI, 0) +           
   PatIndex(T2.ST_CIER_DOCU,'S') * T2.IM_SALD_ORIG ),0), '3',t4.CO_PAGA          
  From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2, #TWDOCU_CLIE_Q32_1 T4, TTDOCU_CNTB T5      
  Where T1.CO_EMPR = T4.CO_EMPR            
   And T1.TI_SITU = 'ACT'            
   And T2.CO_TIDO_ORIG = T4.CO_TIPO_DOCU             
   And T2.NU_DOCU_ORIG = T4.NU_DOCU_CLIE             
   And T1.CO_EMPR = T2.CO_EMPR            
   And T1.CO_UNID_CONC = T2.CO_UNID_CONC            
   And T1.NU_AMAR = T2.NU_AMAR              
   And T1.FE_AMAR = T2.FE_AMAR            
   And T2.CO_EMPR = T4.CO_EMPR            
   And T2.CO_ENTI = T4.CO_CLIE            
   And T2.CO_TIDO_ORIG = T4.CO_TIPO_DOCU            
   And T2.NU_DOCU_ORIG = T4.NU_DOCU_CLIE            
   And T4.TI_DETA = '1'            
      
   And T4.CO_TIPO_DOCU = T5.TI_DOCU            
   And T5.ST_DOCU_0004 = 'S' ")            
           
            
--4.- CANJES            
-- SOLO MUESTRO LOS DOCUMENTOS QUE SE CANJEAN, NO LAS LETRAS GENERADAS            
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO,             
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.CO_TIPO_OPER, T1.FE_CANJ,             
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB,             
  ISNULL(( PatIndex(T4.ST_SIGN,'S') - PatIndex(T4.ST_SIGN,'N') ) *             
  ( PatIndex(T2.ST_CIER_DOCU,'N') * T2.IM_CANJ_MDOC +             
   PatIndex(T2.ST_CIER_DOCU,'S') * T2.IM_SALD_DOCU ),0), '4',t4.CO_PAGA      
  From  TCCANJ_CLIE T1 , TDCANJ_CLIE T2, #TWDOCU_CLIE_Q32_1 T4      
  Where T1.CO_EMPR =   T4.CO_EMPR      
   And T1.TI_SITU != 'ANU'      
   And T1.TI_SITU != 'EXT'      
   And T1.CO_EMPR = T2.CO_EMPR      
   And T1.CO_UNID_CONC = T2.CO_UNID_CONC      
   And T1.CO_CLIE = T2.CO_CLIE      
   And T1.NU_CANJ = T2.NU_CANJ      
   And T1.FE_CANJ = T2.FE_CANJ      
   And T2.CO_EMPR = T4.CO_EMPR      
   And T2.CO_CLIE = T4.CO_CLIE      
   And T2.CO_TIPO_DOCU = T4.CO_TIPO_DOCU      
   And T2.NU_DOCU_CLIE = T4.NU_DOCU_CLIE      
   And T4.TI_DETA = '1'      
 ")            
            
--5.- RENOVACIONES            
-- SOLO MUESTRO LOS DOCUMENTOS QUE SE RENUEVAN, NO LAS LETRAS GENERADAS            
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO,             
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.CO_TIPO_OPER, T1.FE_RENO,            
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB,             
  ISNULL(( PatIndex(T4.ST_SIGN,'S') - PatIndex(T4.ST_SIGN,'N') ) *             
  ( PatIndex(T2.ST_CIER_DOCU,'N') * T2.IM_RENO_MDOC +             
    PatIndex(T2.ST_CIER_DOCU,'S') * T2.IM_SALD_DOCU ),0), '5',t4.CO_PAGA            
  From   TCRENO_CLIE T1 , TDRENO_CLIE T2, #TWDOCU_CLIE_Q32_1 T4      
  Where T1.CO_EMPR =   T4.CO_EMPR            
   And T1.TI_SITU != 'ANU'           
   And T1.TI_SITU != 'EXT'          
   And T2.TI_SITU != 'ANU'            
   And T1.CO_EMPR = T2.CO_EMPR            
   And T1.CO_UNID_CONC = T2.CO_UNID_CONC              
   And T1.CO_CLIE = T2.CO_CLIE            
   And T1.NU_RENO = T2.NU_RENO            
   And T1.FE_RENO = T2.FE_RENO            
   And T2.CO_CLIE = T4.CO_CLIE            
   And T2.CO_TIPO_DOCU = T4.CO_TIPO_DOCU             
   And T2.NU_DOCU_CLIE = T4.NU_DOCU_CLIE      
   And T4.TI_DETA = '1'      
  ")            
            
--6.- APLICACION DE DOCUMENTOS A ANTICIPOS            
-- SOLO MUESTRO LOS DOCUMENTOS QUE SE APLICAN            
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO,             
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, T1.CO_TIPO_OPER, T1.FE_AMAR,            
   T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB,            
  ISNULL( PatIndex(T2.ST_CIER_DOCU, 'N')*            
   ( PatIndex(t4.ST_SIGN,'S') - PatIndex(t4.ST_SIGN,'N') ) * T2.IM_CONV_DOCU +            
  PatIndex(T2.ST_CIER_DOCU, 'S')*            
   ( PatIndex(t4.ST_SIGN,'S') - PatIndex(t4.ST_SIGN,'N') ) * T2.IM_SALD_DOCU,0), '6',t4.CO_PAGA            
  From   TCAMAR_ANTC T1 , TDAMAR_ANTC T2, #TWDOCU_CLIE_Q32_1 T4      
  Where T1.TI_SITU != 'ANU'      
   And T1.TI_SITU != 'EXT'      
   And T1.CO_EMPR = T2.CO_EMPR             
   And T1.CO_UNID_CONC = T2.CO_UNID_CONC             
   And T1.NU_AMAR = T2.NU_AMAR         
   And T1.FE_AMAR = T2.FE_AMAR            
   And T2.CO_EMPR =   T4.CO_EMPR            
   And T2.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'             
   And T2.TI_DOCU_ENTI = T4.CO_TIPO_DOCU             
   And T2.NU_DOCU_ENTI = T4.NU_DOCU_CLIE             
  ")      
            
--7.- ANTICIPOS - PAGOS BANCOS             
            
 EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO,             
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE,            
  T4.CO_TIPO_OPER, T4.FE_EMIS, T4.FE_EMIS, T4.CO_MONE, T4.FA_TIPO_CAMB,             
 ISNULL(T4.IM_TOTA,0), '7',t4.CO_CLIE            
  From TMDOCC_GENE T4 "             
  + @VSCA_WHER + "            
  And   T4.TI_DOCU_ANTC = 'B'            
  And    T4.CO_ESTA_DOCU = 'ACT' ")            
            
--8.- ANTICIPOS - PAGOS BANCOS             
-- ANTICIPO CON APLICACION             
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO,            
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA,CO_PAGA)            
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE,            
  MAX(T4.CO_TIPO_OPER), T7.FE_AMAR, MAX(T4.FE_EMIS), T4.CO_MONE, MAX(T4.FA_TIPO_CAMB),             
  ISNULL(SUM(T7.IM_DETA_NEGA),0), '8',Max(t4.CO_CLIE)      
  From  TMDOCC_GENE T4, TCAMAR_ANTC T6, TDAMAR_ANTC T7 "             
  + @VSCA_WHER + "            
  And   T4.TI_DOCU_ANTC = 'B'            
  And    T4.CO_ESTA_DOCU = 'ACT'             
            
  AND  T6.TI_SITU = 'ACT'            
  AND  T4.CO_EMPR = T6.CO_EMPR            
  AND  T4.CO_CLAV = T6.CO_BANC+T6.NU_CNTA_BANC+T6.AA_BNCO+T6.MM_BNCO+T6.NU_COMP_BANC            
            
  AND  T6.CO_EMPR = T7.CO_EMPR             
   And T6.CO_UNID_CONC = T7.CO_UNID_CONC            
  AND   T6.NU_AMAR = T7.NU_AMAR            
  AND   T6.FE_AMAR = T7.FE_AMAR            
            
  GROUP BY T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE,            
    T4.CO_MONE, T4.CO_CLAV, T7.FE_AMAR")            
      
/** ANTICIPOS CAJA **/             
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, FE_EMIS, FE_VENC,             
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA,CO_PAGA)            
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE,             
  T4.CO_CLIE, T4.FE_EMIS, T4.FE_EMIS, T4.FE_EMIS,              
  T4.CO_MONE, T4.FA_TIPO_CAMB, ISNULL(T4.IM_TOTA,0), '7',t4.CO_CLIE            
  From  TMDOCC_GENE T4 "             
  + @VSCA_WHER + "            
  AND  T4.TI_DOCU_ANTC = 'C'            
  And   T4.CO_ESTA_DOCU = 'ACT' ")            
            
/** ANTICIPOS CAJA - APLICACION**/            
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q32_1             
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, FE_EMIS, FE_VENC,             
  CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA,CO_PAGA)            
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE,             
  T7.FE_AMAR, MAX(T4.FE_EMIS), MAX(T4.FE_EMIS), T4.CO_MONE,            
  MAX(T4.FA_TIPO_CAMB), ISNULL(SUM(T7.IM_DETA_NEGA), 0), '8',Max(t4.CO_CLIE)            
  From  TMDOCC_GENE T4, TCAMAR_ANTC T6, TDAMAR_ANTC T7 "             
  + @VSCA_WHER + "            
  AND   T4.TI_DOCU_ANTC = 'C'            
  And    T4.CO_ESTA_DOCU = 'ACT'             
            
  AND  T6.TI_SITU = 'ACT'            
  AND  T4.CO_EMPR = T6.CO_EMPR             
  And    T4.CO_CLAV = T6.CO_CAJA+T6.AA_CAJA+T6.MM_CAJA+T6.NU_COMP_CAJA            
          
  AND  T6.CO_EMPR = T7.CO_EMPR             
   And T6.CO_UNID_CONC = T7.CO_UNID_CONC            
  AND   T6.NU_AMAR = T7.NU_AMAR             
  AND   T6.FE_AMAR = T7.FE_AMAR            
            
  GROUP BY T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, T4.CO_MONE, T4.CO_CLAV, T7.FE_AMAR")            
      
      
      
      
------------------------------------------------------------------            
----- convierto los importes a la moneda de presentacion            
------------------------------------------------------------------            
      
-- Setea Tipo de cambio --se debe validar por Pantalla que no se mande valor Cero y que la fecha tenga tipo de Cambio Compra Oficial > 0      
IF @ISST_USUA = 'S'            
 Select @VNFA_USUA = @INFA_USUA      
      
ELSE            
 Select @VNFA_USUA = Max(TCFACT_CAMB.FA_CMPR_OFIC)      
 From TCFACT_CAMB, TMPARA_TESO      
 Where TCFACT_CAMB.CO_MONE_BASE = TMPARA_TESO.CO_MONE_DEFA And      
   TCFACT_CAMB.CO_MONE = TMPARA_TESO.CO_MONE_EXTR And      
   TCFACT_CAMB.FE_CAMB = @IDFE_REPO      
      
-- VALIDAR EL APT      
--- Usa el factor de cambio ya sea Digitado o Calculado       
UPDATE #TWDOCU_CLIE_Q32_1      
SET       
 IM_CXCB_CONV = (SELECT dbo.FN_TCFACT_CAMB_Q02(CO_MONE, @ISCO_MONE_PRES, @IDFE_REPO,      
  IM_CXCB, T2.TI_CAMB, T2.TI_ORIG, 'S', T2.CO_MONE_DEFA, T2.CO_MONE_EXTR, @VNFA_USUA)),      
 IM_PAGO_CONV = (SELECT dbo.FN_TCFACT_CAMB_Q02(CO_MONE, @ISCO_MONE_PRES, @IDFE_REPO,      
  IM_PAGO, T2.TI_CAMB, T2.TI_ORIG, 'S', T2.CO_MONE_DEFA, T2.CO_MONE_EXTR, @VNFA_USUA))      
FROM #TWDOCU_CLIE_Q32_1 T1, TMPARA_TESO T2      
WHERE T1.CO_EMPR = T2.CO_EMPR      
      
      
      
 INSERT INTO #TWDOCU_CLIE_Q32_2             
 (CO_EMPR, TI_CLIE, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, FE_EMIS, FE_VENC, FE_DESP, CO_TIPO_OPER, CO_MONE,            
    FA_TIPO_CAMB, IM_CXCB, IM_PAGO, IM_CXCB_CONV, IM_PAGO_CONV,      
 NU_ANTI ,IM_XVEN ,DIAS_0030 ,DIAS_0060 ,DIAS_0090 ,DIAS_0180, DIAS_0360, DIAS_AMAS,CO_PAGA )            
 SELECT T1.CO_EMPR, MAX(ISNULL(T1.TI_CLIE , '')), T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, MIN(T1.FE_EMIS),             
   MAX(T1.FE_VENC), MAX(T1.FE_DESP), MAX(T1.CO_TIPO_OPER), MAX(T1.CO_MONE), MAX(T1.FA_TIPO_CAMB),             
   SUM(T1.IM_CXCB), SUM(T1.IM_PAGO), SUM(T1.IM_CXCB_CONV), SUM(T1.IM_PAGO_CONV),      
   DATEDIFF(DAY, DATEADD(DAY, 30, ISNULL(MAX(T1.FE_DESP), MAX(T1.FE_EMIS))), @IDFE_REPO ),       
   0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,NullIf(RTRIM(LTRIM(Max(t1.CO_PAGA))), '')       
 FROM  #TWDOCU_CLIE_Q32_1 T1      
 GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE      
 HAVING ROUND(SUM(IsNull(T1.IM_CXCB,0)), 2) != ROUND(SUM(IsNull(T1.IM_PAGO,0)), 2)      
      
      
      
      
      
      
 UPDATE #TWDOCU_CLIE_Q32_2      
 SET IM_XVEN = IM_CXCB_CONV - IM_PAGO_CONV      
 WHERE NU_ANTI < 0       
      
 UPDATE #TWDOCU_CLIE_Q32_2      
 SET DIAS_0030 = IM_CXCB_CONV - IM_PAGO_CONV      
 WHERE NU_ANTI >= 0      
 AND NU_ANTI < 30       
      
 UPDATE #TWDOCU_CLIE_Q32_2      
 SET DIAS_0060 = IM_CXCB_CONV - IM_PAGO_CONV      
 WHERE NU_ANTI >= 30      
 AND NU_ANTI < 60       
      
 UPDATE #TWDOCU_CLIE_Q32_2      
 SET DIAS_0090 = IM_CXCB_CONV - IM_PAGO_CONV      
 WHERE NU_ANTI >= 60      
 AND NU_ANTI < 90       
      
 UPDATE #TWDOCU_CLIE_Q32_2      
 SET DIAS_0180 = IM_CXCB_CONV - IM_PAGO_CONV      
 WHERE NU_ANTI >= 90      
 AND NU_ANTI < 180      
      
 UPDATE #TWDOCU_CLIE_Q32_2      
 SET DIAS_0360 = IM_CXCB_CONV - IM_PAGO_CONV      
 WHERE NU_ANTI >= 180      
 AND NU_ANTI < 360      
      
 UPDATE #TWDOCU_CLIE_Q32_2      
 SET DIAS_AMAS = IM_CXCB_CONV - IM_PAGO_CONV      
 WHERE NU_ANTI >= 360      
      
      
      
      
 -- Agrupar Clientes -- CASO != AGRUP PAGADOR       
If IsNull(@ISTI_REPO, '') != '3'      
Begin      
 INSERT INTO #TWDOCU_CLIE_Q32_3      
 (CO_EMPR, TI_CLIE, CO_CLIE, CO_MONE,            
 TO_XVEN ,TO_DIAS_0030, TO_DIAS_0060, TO_DIAS_0090, TO_DIAS_0180, TO_DIAS_0360, TO_DIAS_AMAS,      
 TO_DEUD_VENC,      
 TO_DEUD_CONS )      
 SELECT CO_EMPR, 'T21', CO_CLIE, @ISCO_MONE_PRES CO_MONE,      
  Sum(IM_XVEN), Sum(DIAS_0030), Sum(DIAS_0060), Sum(DIAS_0090), Sum(DIAS_0180), Sum(DIAS_0360), Sum(DIAS_AMAS),      
  Sum(DIAS_0030) + Sum(DIAS_0060)+  Sum(DIAS_0090)+ Sum(DIAS_0180) + Sum(DIAS_0360) + Sum(DIAS_AMAS),      
  Sum(IM_XVEN)  +  Sum(DIAS_0030) + Sum(DIAS_0060)+  Sum(DIAS_0090)+ Sum(DIAS_0180) + Sum(DIAS_0360) + Sum(DIAS_AMAS) AS TO_DEUD_CONS      
 FROM #TWDOCU_CLIE_Q32_2      
 GROUP BY CO_EMPR, TI_CLIE, CO_CLIE      
End       
      
If IsNull(@ISTI_REPO, '') = '3' --- SOLO PARA CASO AGRUP PAGADOR      
Begin       
      
--- TODOS LOS CODIGOS DE PAGADOR QUE NO TIENEN DATO DEBEN ESTAR EN NULO      
/*Update #TWDOCU_CLIE_Q32_2      
Set CO_PAGA = NULL      
Where RTRIM(LTRIM(CO_PAGA)) = ''*/      
      
 INSERT INTO #TWDOCU_CLIE_Q32_3      
 (CO_EMPR, TI_CLIE, CO_CLIE, CO_MONE,            
 TO_XVEN ,TO_DIAS_0030, TO_DIAS_0060, TO_DIAS_0090, TO_DIAS_0180, TO_DIAS_0360, TO_DIAS_AMAS,      
 TO_DEUD_VENC,      
 TO_DEUD_CONS )      
 SELECT CO_EMPR, 'T21', CO_PAGA, @ISCO_MONE_PRES CO_MONE,      
  Sum(IM_XVEN), Sum(DIAS_0030), Sum(DIAS_0060), Sum(DIAS_0090), Sum(DIAS_0180), Sum(DIAS_0360), Sum(DIAS_AMAS),      
  Sum(DIAS_0030) + Sum(DIAS_0060)+  Sum(DIAS_0090)+ Sum(DIAS_0180) + Sum(DIAS_0360) + Sum(DIAS_AMAS),      
  Sum(IM_XVEN)  +  Sum(DIAS_0030) + Sum(DIAS_0060)+  Sum(DIAS_0090)+ Sum(DIAS_0180) + Sum(DIAS_0360) + Sum(DIAS_AMAS) AS TO_DEUD_CONS      
 FROM #TWDOCU_CLIE_Q32_2      
 GROUP BY CO_EMPR, CO_PAGA---TI_CLIE, CO_CLIE      
      
End       
      
      
      
      
 If  @ISTI_REPO =  '1' --- Por Cliente      
   --- Selecciono los TOP 21 ---       
  BEGIN       
   INSERT INTO #TWLIE_Q32_0      
   (CO_EMPR, CO_CLIE )      
   SELECT TOP 21 CO_EMPR, CO_CLIE       
   FROM #TWDOCU_CLIE_Q32_3      
   WHERE      
   TO_DEUD_CONS <> 0      
   ORDER BY TO_DEUD_CONS DESC       
  END      
      
 If  @ISTI_REPO =  '2' --- Grupo Economico      
 Begin      
      
  Update #TWDOCU_CLIE_Q32_3       
  Set       
  #TWDOCU_CLIE_Q32_3.CO_GRUP_ECON = TMCLIE.CO_GRUP_ECON      
  From TMCLIE      
  Where       
  TMCLIE.CO_EMPR = #TWDOCU_CLIE_Q32_3.CO_EMPR and      
  TMCLIE.CO_CLIE = #TWDOCU_CLIE_Q32_3.CO_CLIE      
 ------------------------------------------      
  INSERT INTO #TWLIE_Q32_0      
     (CO_EMPR,    CO_CLIE,    CO_GRUP_ECON,    TO_DEUD_VENC )      
  SELECT TOP 21      
    TMDOCU_AGRU.CO_EMPR, TMDOCU_AGRU.CO_CLIE, TMDOCU_AGRU.CO_GRUP_ECON, TMDOCU_AGRU.TO_DEUD_VENC      
  FROM (      
    SELECT       
    CO_EMPR,    Null CO_CLIE,   CO_GRUP_ECON,    SUM(TO_DEUD_CONS) AS TO_DEUD_VENC      
    FROM #TWDOCU_CLIE_Q32_3      
 WHERE      
    TO_DEUD_CONS <> 0  AND      
    CO_GRUP_ECON IS NOT NULL      
    GROUP BY CO_EMPR, CO_GRUP_ECON      
     UNION ALL       
    SELECT CO_EMPR, CO_CLIE, NULL CO_GRUP_ECON, SUM(TO_DEUD_CONS)      
    FROM #TWDOCU_CLIE_Q32_3      
    WHERE      
    TO_DEUD_CONS <> 0  AND      
    CO_GRUP_ECON IS NULL      
    GROUP BY CO_EMPR, CO_CLIE) TMDOCU_AGRU      
      
    WHERE LEN(TMDOCU_AGRU.CO_GRUP_ECON) > 0 -- IS NOT NULL      
   ORDER BY TMDOCU_AGRU.TO_DEUD_VENC DESC      
 End      
      
If @ISTI_REPO = '3'      
Begin       
      
INSERT INTO #TWLIE_Q32_0      
   (CO_EMPR, CO_PAGA )      
   SELECT TOP 21       
CO_EMPR, CO_CLIE      
   FROM #TWDOCU_CLIE_Q32_3      
   WHERE TO_DEUD_CONS <> 0      
         And CO_CLIE IS NOT NULL       
   ORDER BY TO_DEUD_CONS DESC       
End       
      
--SELECT 'PERCY',* FROM #TWLIE_Q32_0      
      
--If  @ISTI_REPO =  '3' --- Pagador       
-- BEGIN       
-- END      
--       
--Actualizo, el Nombre de con el Codigo Comodin --99999999999      
If IsNull(@ISTI_REPO,'') != '3'      
Begin       
 Update #TWDOCU_CLIE_Q32_3 Set CO_CLIE = '99999999999' , TI_CLIE = 'U22'      
 Where       
 #TWDOCU_CLIE_Q32_3.CO_GRUP_ECON IS NULL AND       
 Not Exists (select #TWLIE_Q32_0.CO_CLIE      
     From  #TWLIE_Q32_0      
     Where       
     #TWLIE_Q32_0.CO_EMPR = #TWDOCU_CLIE_Q32_3.CO_EMPR and      
     #TWLIE_Q32_0.CO_CLIE = #TWDOCU_CLIE_Q32_3.CO_CLIE      
     )      
      
 If  @ISTI_REPO =  '2'      
 Begin      
   --Actualizo, el Nombre de con el Codigo Comodin --99999999999      
   Update #TWDOCU_CLIE_Q32_3 Set CO_CLIE = '99999999999' , TI_CLIE = 'U22'      
   Where       
   #TWDOCU_CLIE_Q32_3.CO_GRUP_ECON IS not NULL AND       
   Not Exists (select #TWLIE_Q32_0.CO_CLIE      
       From  #TWLIE_Q32_0      
       Where       
       #TWLIE_Q32_0.CO_EMPR = #TWDOCU_CLIE_Q32_3.CO_EMPR and      
       #TWLIE_Q32_0.CO_GRUP_ECON = #TWDOCU_CLIE_Q32_3.CO_GRUP_ECON      
       )      
      
 End      
End       
      
If IsNull(@ISTI_REPO,'') = '3'      
Begin       
      
Update #TWDOCU_CLIE_Q32_3 Set CO_CLIE = '99999999999', TI_CLIE = 'U22'      
From #TWDOCU_CLIE_Q32_3 t1      
Where Not Exists (select tX.CO_PAGA      
      From #TWLIE_Q32_0 tX      
      Where tX.CO_EMPR = t1.CO_EMPR       
                  And tX.CO_PAGA = t1.CO_CLIE)      
      
End       
      
      
      
 -- Cargo Montos Totales % Deuda Vigente      
 Select       
 @VNTO_DEUD_VENC = Sum(TO_DIAS_0030) + Sum(TO_DIAS_0060)+  Sum(TO_DIAS_0090)+ Sum(TO_DIAS_0180) + Sum(TO_DIAS_0360) + Sum(TO_DIAS_AMAS),      
 @VNTO_DEUD_CONS = Sum(TO_DEUD_CONS)      
 From #TWDOCU_CLIE_Q32_3      
      
 ----      
 Update #TWDOCU_CLIE_Q32_3      
 set PC_DEUD_VENC = TO_DEUD_VENC / @VNTO_DEUD_VENC      
 Where      
 @VNTO_DEUD_VENC <> 0      
      
      
      
      
IF @ISFO_REPO = 'D'  ---------- DETALLADO      
 BEGIN        
  If  @ISTI_REPO =  '1'  ------------ TIPO DE REPORTE AGRUPADO POR CLIENTE       
    BEGIN        
     IF @ISMO_REPO = 'P'  ---------- POR PANTALLA      
      BEGIN        
       Select         
       T1.TI_CLIE,   T1.CO_CLIE,    T2.NO_CLIE,        
       T1.TO_DIAS_0030, T1.TO_DIAS_0060,  T1.TO_DIAS_0090,         
       T1.TO_DIAS_0180, T1.TO_DIAS_0360,  T1.TO_DIAS_AMAS,         
       T1.TO_DEUD_VENC, T1.PC_DEUD_VENC *100, T1.TO_XVEN,         
       T1.TO_DEUD_CONS          
       From   #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Union  All        
       Select         
       T1.TI_CLIE,     T1.CO_CLIE ,    Max('Otros Vencidos'),        
       Sum(T1.TO_DIAS_0030),  Sum(T1.TO_DIAS_0060),  Sum(T1.TO_DIAS_0090),          
       Sum(T1.TO_DIAS_0180),   Sum(T1.TO_DIAS_0360),  Sum(T1.TO_DIAS_AMAS),          
       Sum(T1.TO_DEUD_VENC),   Sum(T1.PC_DEUD_VENC)* 100, 0,        
       Sum(T1.TO_DEUD_VENC)        
       -- Sum(T1.PC_DEUD_VENC)* 100, Sum(T1.TO_XVEN), Sum(T1.TO_DEUD_CONS)        
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       --   ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
       Union  All        
       Select         
       T1.TI_CLIE,     T1.CO_CLIE,     Max('Creditos Vigentes'),        
       0,       0,       0,         
       0,       0,       0,         
       0,        0,       Sum(T1.TO_XVEN)        
       , Sum(T1.TO_XVEN) --Sum(T1.TO_DIAS_0030)+Sum(T1.TO_DIAS_0060)+ Sum(T1.TO_DIAS_0090)+ Sum(T1.TO_DIAS_0180)+Sum(T1.TO_DIAS_0360)+Sum(T1.TO_DIAS_AMAS)        
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
      END        
     ELSE IF @ISMO_REPO = 'T'   ------------ TIPO TABLA       
      BEGIN        
       Select         
       T1.TI_CLIE,       T1.CO_CLIE,       T2.NO_CLIE,        
       CONVERT(INT,T1.TO_DIAS_0030/1000),  CONVERT(INT,T1.TO_DIAS_0060/1000),  CONVERT(INT,T1.TO_DIAS_0090/1000),         
       CONVERT(INT,T1.TO_DIAS_0180/1000),  CONVERT(INT,T1.TO_DIAS_0360/1000),  CONVERT(INT,T1.TO_DIAS_AMAS/1000),         
       CONVERT(INT,T1.TO_DEUD_VENC/1000),  CONVERT(INT,T1.PC_DEUD_VENC *100),  CONVERT(INT,T1.TO_XVEN/1000),         
       CONVERT(INT,T1.TO_DEUD_CONS/1000)        
      
       From #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Union  All        
       Select         
       T1.TI_CLIE,        T1.CO_CLIE,        Max('Otros Vencidos'),        
       CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000), CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000), CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000),         
       CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000), CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000), CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000),         
       CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000), CONVERT(INT,Sum(T1.PC_DEUD_VENC)* 100), 0,         
       CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000)        
      
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       --ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
       UNION ALL        
       Select         
       T1.TI_CLIE,        T1.CO_CLIE,        Max('Creditos Vigentes'),        
       0,          0,          0,           
       0,          0,          0,        
       0,          0,          CONVERT(INT,Sum(T1.TO_XVEN)/1000),         
       CONVERT(INT,Sum(T1.TO_XVEN)/1000)        
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
      END        
     ELSE IF @ISMO_REPO = 'H'   ------------ TIPO DE REPORTE ANALISIS DE DE DEUDORES/PAGADORES      
      BEGIN  --- ppercy      
       Select         
       T1.TI_CLIE,       T1.CO_CLIE,       T2.NO_CLIE,        
       CONVERT(NUMERIC(16,0),T1.TO_DIAS_0030/1000),  CONVERT(NUMERIC(16,0),T1.TO_DIAS_0060/1000),  CONVERT(NUMERIC(16,0),T1.TO_DIAS_0090/1000),         
       CONVERT(NUMERIC(16,0),T1.TO_DIAS_0180/1000),  CONVERT(NUMERIC(16,0),T1.TO_DIAS_0360/1000),  CONVERT(NUMERIC(16,0),T1.TO_DIAS_AMAS/1000),         
       CONVERT(NUMERIC(16,0),T1.TO_DEUD_VENC/1000),  CONVERT(NUMERIC(16,0),T1.TO_DEUD_CONS/1000)        
      
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Union  All        
       Select         
       T1.TI_CLIE,        T1.CO_CLIE,        Max('Otros Vencidos'),        
       CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0030)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0060)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0090)/1000),         
       CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0180)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0360)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_AMAS)/1000),         
       CONVERT(NUMERIC(16,0),Sum(T1.TO_DEUD_VENC)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DEUD_VENC)/1000)        
      
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       --ORDER BY 1, 10 DESC, 12 DESC, 13 DESC        
       UNION ALL        
       Select         
       T1.TI_CLIE,        T1.CO_CLIE,        Max('Creditos Vigentes'),        
       0,          0,          0,           
       0,          0,          0,        
       0, CONVERT(NUMERIC(16,0),Sum(T1.TO_DEUD_CONS)/1000)                 
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 11  DESC      
/*      
       Select         
       T1.TI_CLIE,       T1.CO_CLIE,       T2.NO_CLIE,        
       CONVERT(INT,T1.TO_DIAS_0030/1000),  CONVERT(INT,T1.TO_DIAS_0060/1000),  CONVERT(INT,T1.TO_DIAS_0090/1000),         
       CONVERT(INT,T1.TO_DIAS_0180/1000),  CONVERT(INT,T1.TO_DIAS_0360/1000),  CONVERT(INT,T1.TO_DIAS_AMAS/1000),         
       CONVERT(INT,T1.TO_DEUD_VENC/1000),  CONVERT(INT,T1.TO_DEUD_CONS/1000)        
      
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Union  All        
       Select         
       T1.TI_CLIE,        T1.CO_CLIE,        Max('Otros Vencidos'),        
       CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000), CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000), CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000),         
       CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000), CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000), CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000),         
       CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000), CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000)        
      
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       --ORDER BY 1, 10 DESC, 12 DESC, 13 DESC        
       UNION ALL        
       Select         
       T1.TI_CLIE,        T1.CO_CLIE,        Max('Creditos Vigentes'),        
       0,          0,          0,           
       0,          0,          0,        
       0, CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)                 
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 11  DESC*/      
      END        
    END        
  Else If  @ISTI_REPO =  '2'   ------------ TIPO DE REPORTE AGRUPADO POR GRUPO ECONOMICO       
    BEGIN        
     IF @ISMO_REPO = 'P'   ------------ PANTALLA      
      BEGIN        
       Select T1.TI_CLIE, T1.CO_CLIE, T2.NO_CLIE,         
       T1.TO_DIAS_0030, T1.TO_DIAS_0060, T1.TO_DIAS_0090, T1.TO_DIAS_0180, T1.TO_DIAS_0360, T1.TO_DIAS_AMAS, T1.TO_DEUD_VENC,         
       T1.PC_DEUD_VENC *100, T1.TO_XVEN, T1.TO_DEUD_CONS          
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_GRUP_ECON IS NULL AND        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
      
       Union  All        
       Select  T1.TI_CLIE, T1.CO_GRUP_ECON, Max(T2.DE_GRUP_ECON),         
       Sum(T1.TO_DIAS_0030), Sum(T1.TO_DIAS_0060), Sum(T1.TO_DIAS_0090), Sum(T1.TO_DIAS_0180), Sum(T1.TO_DIAS_0360), Sum(T1.TO_DIAS_AMAS), Sum(T1.TO_DEUD_VENC),         
       Sum(T1.PC_DEUD_VENC *100), Sum(T1.TO_XVEN), Sum(T1.TO_DEUD_CONS)        
       From  #TWDOCU_CLIE_Q32_3 T1, TTGRUP_ECON T2        
       WHERE        
       T1.CO_GRUP_ECON IS NOT NULL AND        
       T1.CO_GRUP_ECON = T2.CO_GRUP_ECON AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Group by T1.TI_CLIE, T1.CO_GRUP_ECON         
      
       Union  All        
       Select T1.TI_CLIE, T1.CO_CLIE , Max('Otros Vencidos'),        
       Sum(T1.TO_DIAS_0030), Sum(T1.TO_DIAS_0060), Sum(T1.TO_DIAS_0090), Sum(T1.TO_DIAS_0180), Sum(T1.TO_DIAS_0360), Sum(T1.TO_DIAS_AMAS), Sum(T1.TO_DEUD_VENC),         
       Sum(T1.PC_DEUD_VENC)* 100, 0,Sum(T1.TO_DIAS_0030)+Sum(T1.TO_DIAS_0060)+ Sum(T1.TO_DIAS_0090)+ Sum(T1.TO_DIAS_0180)+Sum(T1.TO_DIAS_0360)+Sum(T1.TO_DIAS_AMAS)        
       -- Sum(T1.PC_DEUD_VENC)* 100, Sum(T1.TO_XVEN), Sum(T1.TO_DEUD_CONS)        
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       --ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
      
       Union  All        
       Select T1.TI_CLIE, T1.CO_CLIE , Max('Creditos Vigentes'),        
       0,0, 0, 0,0, 0, 0,         
       0, Sum(T1.TO_XVEN),Sum(T1.TO_XVEN)--, Sum(T1.TO_DIAS_0030)+Sum(T1.TO_DIAS_0060)+ Sum(T1.TO_DIAS_0090)+ Sum(T1.TO_DIAS_0180)+Sum(T1.TO_DIAS_0360)+Sum(T1.TO_DIAS_AMAS)        
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
      END        
     ELSE  IF @ISMO_REPO = 'T'   ------------ TIPO TABLA       
      BEGIN        
       Select         
       T1.TI_CLIE,      T1.CO_CLIE,      T2.NO_CLIE,         
       CONVERT(INT,T1.TO_DIAS_0030/1000),    CONVERT(INT,T1.TO_DIAS_0060/1000),    CONVERT(INT,T1.TO_DIAS_0090/1000),         
       CONVERT(INT,T1.TO_DIAS_0180/1000),    CONVERT(INT,T1.TO_DIAS_0360/1000),    CONVERT(INT,T1.TO_DIAS_AMAS/1000),         
       CONVERT(INT,T1.TO_DEUD_VENC/1000),     CONVERT(INT,T1.PC_DEUD_VENC *100),   CONVERT(INT,T1.TO_XVEN/1000),         
       CONVERT(INT,T1.TO_DEUD_CONS/1000)          
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_GRUP_ECON IS NULL AND        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Union  All        
       Select         
       T1.TI_CLIE,      T1.CO_GRUP_ECON,    Max(T2.DE_GRUP_ECON),         
       CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000),         
       CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000),         
       CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000),   CONVERT(INT,Sum(T1.PC_DEUD_VENC *100)), CONVERT(INT,Sum(T1.TO_XVEN)/1000),         
       CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)        
       From  #TWDOCU_CLIE_Q32_3 T1, TTGRUP_ECON T2        
       WHERE        
       T1.CO_GRUP_ECON IS NOT NULL AND        
       T1.CO_GRUP_ECON = T2.CO_GRUP_ECON AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Group by T1.TI_CLIE, T1.CO_GRUP_ECON         
       Union  All        
       Select         
       T1.TI_CLIE,      T1.CO_CLIE,      Max('Otros Vencidos'),        
       CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000),         
       CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000),         
       CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000),   CONVERT(INT,Sum(T1.PC_DEUD_VENC)* 100/1000), 0,             
       0        
       -- Sum(T1.PC_DEUD_VENC)* 100, Sum(T1.TO_XVEN), Sum(T1.TO_DEUD_CONS)        
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       --ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
       Union  All        
       Select         
       T1.TI_CLIE,      T1.CO_CLIE,      Max('Creditos Vigentes'),        
       0,        0,   0,         
       0,        0,        0,         
       0,         0,        CONVERT(INT,Sum(T1.TO_XVEN)/1000),         
       CONVERT(INT,Sum(T1.TO_DIAS_0030)+Sum(T1.TO_DIAS_0060)+ Sum(T1.TO_DIAS_0090)+ Sum(T1.TO_DIAS_0180)+Sum(T1.TO_DIAS_0360)+Sum(T1.TO_DIAS_AMAS)/1000)        
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'         
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
      END        
      IF @ISMO_REPO = 'H'   ------------ TIPO DE REPORTE ANALISIS DE DE DEUDORES/PAGADORES      
      BEGIN        
       Select T1.TI_CLIE,         T1.CO_CLIE,           T2.NO_CLIE,         
       CONVERT(INT,T1.TO_DIAS_0030/1000),   CONVERT(INT,T1.TO_DIAS_0060/1000),     CONVERT(INT,T1.TO_DIAS_0090/1000),         
       CONVERT(INT,T1.TO_DIAS_0180/1000),   CONVERT(INT,T1.TO_DIAS_0360/1000),     CONVERT(INT,T1.TO_DIAS_AMAS/1000),         
       CONVERT(INT,T1.TO_DEUD_VENC/1000),    CONVERT(INT,T1.TO_DEUD_CONS/1000)                      
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_GRUP_ECON IS NULL AND        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Union  All        
       Select T1.TI_CLIE,          T1.CO_GRUP_ECON,        Max(T2.DE_GRUP_ECON),         
       CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000),         
       CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000),         
       CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000),   CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)                   
       From  #TWDOCU_CLIE_Q32_3 T1, TTGRUP_ECON T2        
       WHERE        
       T1.CO_GRUP_ECON IS NOT NULL AND        
       T1.CO_GRUP_ECON = T2.CO_GRUP_ECON AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Group by T1.TI_CLIE, T1.CO_GRUP_ECON         
       Union  All        
       Select T1.TI_CLIE,       T1.CO_CLIE,      Max('Otros Vencidos'),        
       CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000),    CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000),         
       CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000),    CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000),   CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000),         
       CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000),    CONVERT(INT,Sum(T1.TO_DEUD_VENC)/1000)        
      
       -- Sum(T1.PC_DEUD_VENC)* 100, Sum(T1.TO_XVEN), Sum(T1.TO_DEUD_CONS)        
       From  #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       --ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
       Union  All        
       Select T1.TI_CLIE,       T1.CO_CLIE,      Max('Creditos Vigentes'),        
       0,     0,        0,         
       0,         0,        0,         
       0,         CONVERT(INT,Sum(T1.TO_XVEN)/1000)        
       From #TWDOCU_CLIE_Q32_3 T1, TMCLIE T2        
       WHERE        
       T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE AND        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 11  DESC       
      END        
      END        
  Else If  @ISTI_REPO =  '3'   ---- ESTA SERIA PARA PAGADOR PERCY      
      
      
    BEGIN      
      
     IF @ISMO_REPO = 'P'  ---------- POR PANTALLA      
      BEGIN        
       Select         
       T1.TI_CLIE,   T1.CO_CLIE,    T2.NO_CLIE,        
       T1.TO_DIAS_0030, T1.TO_DIAS_0060,  T1.TO_DIAS_0090,         
       T1.TO_DIAS_0180, T1.TO_DIAS_0360,  T1.TO_DIAS_AMAS,         
       T1.TO_DEUD_VENC, T1.PC_DEUD_VENC *100, T1.TO_XVEN,         
       T1.TO_DEUD_CONS          
       From   #TWDOCU_CLIE_Q32_3 T1 LEFT OUTER JOIN TMCLIE_TESO T2        
       ON (T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE )      
                            WHERE        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Union  All        
       Select         
       T1.TI_CLIE,     T1.CO_CLIE ,    Max('Otros Vencidos'),        
       Sum(T1.TO_DIAS_0030),  Sum(T1.TO_DIAS_0060),  Sum(T1.TO_DIAS_0090),          
       Sum(T1.TO_DIAS_0180),   Sum(T1.TO_DIAS_0360),  Sum(T1.TO_DIAS_AMAS),          
       Sum(T1.TO_DEUD_VENC),   Sum(T1.PC_DEUD_VENC)* 100, 0,        
       Sum(T1.TO_DEUD_VENC)        
             
       From  #TWDOCU_CLIE_Q32_3 T1      
       WHERE        
             
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
              Union  All        
       Select         
       T1.TI_CLIE,     T1.CO_CLIE,     Max('Creditos Vigentes'),        
       0,       0,       0,         
       0,       0,       0,         
       0,        0,       Sum(T1.TO_XVEN)        
       , Sum(T1.TO_XVEN)       
       From  #TWDOCU_CLIE_Q32_3 T1      
       WHERE        
             
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
      END        
     ELSE IF @ISMO_REPO = 'T'   ------------ TIPO TABLA       
      BEGIN       
      
       Select         
       T1.TI_CLIE,       T1.CO_CLIE,       T2.NO_CLIE,        
       CONVERT(NUMERIC(16,0),T1.TO_DIAS_0030/1000),  CONVERT(NUMERIC(16,0),T1.TO_DIAS_0060/1000),  CONVERT(NUMERIC(16,0),T1.TO_DIAS_0090/1000),         
       CONVERT(NUMERIC(16,0),T1.TO_DIAS_0180/1000),  CONVERT(NUMERIC(16,0),T1.TO_DIAS_0360/1000),  CONVERT(NUMERIC(16,0),T1.TO_DIAS_AMAS/1000),         
       CONVERT(NUMERIC(16,0),T1.TO_DEUD_VENC/1000),  CONVERT(NUMERIC(16,0),T1.PC_DEUD_VENC *100),  CONVERT(NUMERIC(16,0),T1.TO_XVEN/1000),         
       CONVERT(NUMERIC(16,0),T1.TO_DEUD_CONS/1000)        
      
       From #TWDOCU_CLIE_Q32_3 T1 LEFT OUTER JOIN  TMCLIE_TESO T2        
       ON (T1.CO_EMPR = T2.CO_EMPR AND        
       T1.CO_CLIE = T2.CO_CLIE )      
                            WHERE        
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'T21'        
       Union  All        
       Select         
       T1.TI_CLIE,        T1.CO_CLIE,        Max('Otros Vencidos'),        
       CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0030)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0060)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0090)/1000),         
       CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0180)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_0360)/1000), CONVERT(NUMERIC(16,0),Sum(T1.TO_DIAS_AMAS)/1000),         
       CONVERT(NUMERIC(16,0),Sum(T1.TO_DEUD_VENC)/1000), CONVERT(NUMERIC(16,0),Sum(T1.PC_DEUD_VENC)* 100), 0,         
       CONVERT(NUMERIC(16,0),Sum(T1.TO_DEUD_VENC)/1000)        
      
       From  #TWDOCU_CLIE_Q32_3 T1      
       WHERE        
             
       T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
              
       UNION ALL        
       Select         
       T1.TI_CLIE,        T1.CO_CLIE,        Max('Creditos Vigentes'),        
       0,          0,          0,           
       0,          0,          0,        
       0,          0,    CONVERT(NUMERIC(16,0),Sum(T1.TO_XVEN)/1000),         
       CONVERT(NUMERIC(16,0),Sum(T1.TO_XVEN)/1000)        
       From  #TWDOCU_CLIE_Q32_3 T1       
       WHERE        
           T1.TO_DEUD_CONS <> 0 AND        
       T1.TI_CLIE = 'U22'        
       Group by T1.TI_CLIE, T1.CO_CLIE        
       ORDER BY 1, 10 DESC, 12 DESC, 13 DESC         
      END        
      
    END      
         
 END        
Else IF @ISFO_REPO = 'R'        
 begin        
  IF @ISMO_REPO = 'P'        
    BEGIN        
     INSERT INTO  #TWCLIE_RESU         
     (TI_INGR, AL_TOTA, AL_VIGE, AL_VENC,         
     DE_01_30, DE_31_60, DE_61_90, DE_91_180,         
     DE_181_360, DE_361_MAS)        
     Select         
     'CONSOLIDADO',        
     CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000) as 'TOTAL',         
     CONVERT(INT,Sum(T1.TO_XVEN)/1000) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000) AS ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD) AND        
     T1.TO_DEUD_CONS <> 0         
     union all        
     Select         
     'REEMBOLSABLE',        
     CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000) as 'TOTAL',         
     CONVERT(INT,Sum(T1.TO_XVEN)/1000) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000) AS ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD WHERE TI_DEUD = 'REE') AND        
     T1.TO_DEUD_CONS <> 0         
     UNION ALL        
     Select         
     'INGRESOS PROPIOS',        
     CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000) as 'TOTAL',         
     CONVERT(INT,Sum(T1.TO_XVEN)/1000) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000) AS ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD WHERE TI_DEUD = 'ING') AND        
     T1.TO_DEUD_CONS <> 0         
      
     --SELECT * FROM  TWCLIE_RESU        
    END        
  ELSE  IF @ISMO_REPO = 'T'      
    BEGIN        
     INSERT INTO  #TWCLIE_RESU         
     (TI_INGR, AL_TOTA, AL_VIGE, AL_VENC,         
     DE_01_30, DE_31_60, DE_61_90, DE_91_180,         
     DE_181_360, DE_361_MAS)        
     Select         
     'CONSOLIDADO',        
     CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000) as 'TOTAL',         
     CONVERT(INT,Sum(T1.TO_XVEN)/1000) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000) AS ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD) AND        
     T1.TO_DEUD_CONS <> 0         
     union all        
     Select         
     'CONSOLIDADO %',         
     '100',        
     CONVERT(INT,Sum(T1.TO_XVEN)/1000)/(CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)/100) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000)/(CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)/100) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/(Sum(T1.TO_DEUD_VENC)/100)) AS ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/(Sum(T1.TO_DEUD_VENC)/100)) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/(Sum(T1.TO_DEUD_VENC)/100)) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/(Sum(T1.TO_DEUD_VENC)/100)) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/(Sum(T1.TO_DEUD_VENC)/100)) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/(Sum(T1.TO_DEUD_VENC)/100)) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD) AND        
     T1.TO_DEUD_CONS <> 0        
     union all        
     Select         
     'REEMBOLSABLE',        
     CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000) as 'TOTAL',         
     CONVERT(INT,Sum(T1.TO_XVEN)/1000) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000) AS ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD WHERE TI_DEUD = 'REE') AND        
     T1.TO_DEUD_CONS <> 0         
     UNION ALL        
     Select         
     'REEMBOLSABLE %',         
     '100' ,         
     CONVERT(INT,Sum(T1.TO_XVEN)/1000)/(CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)/100) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000)/(CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)/100) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/(Sum(T1.TO_DEUD_VENC)/100)) ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/(Sum(T1.TO_DEUD_VENC)/100)) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/(Sum(T1.TO_DEUD_VENC)/100)) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/(Sum(T1.TO_DEUD_VENC)/100)) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/(Sum(T1.TO_DEUD_VENC)/100)) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/(Sum(T1.TO_DEUD_VENC)/100)) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD WHERE TI_DEUD = 'REE') AND        
     T1.TO_DEUD_CONS <> 0        
     union all        
     Select         
     'INGRESOS PROPIOS',        
     CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000) as 'TOTAL',         
     CONVERT(INT,Sum(T1.TO_XVEN)/1000) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/1000) AS ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/1000) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/1000) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/1000) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/1000) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/1000) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD WHERE TI_DEUD = 'ING') AND        
     T1.TO_DEUD_CONS <> 0         
     UNION ALL        
     Select         
     'INGRESOS PROPIOS %',         
     '100',        
     CONVERT(INT,Sum(T1.TO_XVEN)/1000)/(CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)/100) as 'VIGENTES',         
     CONVERT(INT,SUM(T1.TO_DEUD_VENC)/1000)/(CONVERT(INT,Sum(T1.TO_DEUD_CONS)/1000)/100) as 'VENCIDO',        
     CONVERT(INT,Sum(T1.TO_DIAS_0030)/(Sum(T1.TO_DEUD_VENC)/100)) AS ' 1 A  30 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0060)/(Sum(T1.TO_DEUD_VENC)/100)) AS ' 31 A  60 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0090)/(Sum(T1.TO_DEUD_VENC)/100)) AS ' 61 A  90 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0180)/(Sum(T1.TO_DEUD_VENC)/100)) AS '91 A 180 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_0360)/(Sum(T1.TO_DEUD_VENC)/100)) AS '181 A 360 DIAS',         
     CONVERT(INT,Sum(T1.TO_DIAS_AMAS)/(Sum(T1.TO_DEUD_VENC)/100)) AS '361 DIAS A MAS'          
     From  #TWDOCU_CLIE_Q32_3 T1        
     WHERE        
     CO_TIPO_DOCU IN (SELECT TI_DOCU FROM TMDOCU_DEUD WHERE TI_DEUD = 'ING') AND        
     T1.TO_DEUD_CONS <> 0        
     SELECT * FROM #TWCLIE_RESU      
    END         
 END      
      
      
      
      
RETURN            
/*------------------------- FIN ------------------------------ */      
      
      
      
      
      
      
GO
